﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaintenanceLogFileSize]
#-- Purpose:		Maintains the size of the log file and the vlf increments
#--					in the log file to reduce fragmentation and speed up 
#--					performance and database startup.
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#-- Readings:		http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
#--					http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
#--					http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
#--					http://sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
#--					http://www.karaszi.com/SQLServer/info_dont_shrink.asp
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaintenanceLogFileSize]
(
	@log_pct_total			int		= 10, 
	@log_pct_used			int		= 25,
	@min_disk_free_pct		int		= 15,
	@debug_only				bit		= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@database_name sysname, @sSQL varchar(MAX), @vlf_optimal int, @vlf_minimal int,
			@pct_log_total float, @pct_log_used float
DECLARE		@diskspace TABLE (dbd_drive nvarchar(256), dbd_capacityMB varchar(256), dbd_usedMB varchar(256), dbd_freeMB varchar(256), dbd_free_pct varchar(6))
DECLARE		@logsize TABLE (database_id int, file_id int, name sysname, physical_name nvarchar(260), logfile_size_MB bigint, logfile_count int)
DECLARE		@file_size TABLE (database_id int, Fileid int, FileGroup int, TotalExtents int, UsedExtents int, name sysname, physical_name nchar(520))
DECLARE		@log_info TABLE (database_id int, FileID int, FileSize numeric(20,0), StartOffset bigint, FSeqNo bigint, Status tinyint, Parity tinyint, CreateLSN numeric(25,0))
DECLARE		@progname sysname
DECLARE	@results TABLE
(
	log_action				varchar(30),
	act_drive				char(2),
	database_id				int,
	name					sysname,
	physical_name			nvarchar(260),
	total_MB				bigint,
	used_MB					bigint,
	logfile_size_MB			bigint,
	logfile_count			int,
	current_vlf_count		int,
	suggested_logsize_MB	bigint,
	suggested_vlfsize_MB	int,
	suggested_vlf_count		int,
	log_incremental_size_MB	int,
	log_incremental_count	int,
	recovery_model			sysname
)

--- Define the name of this program (for logs)
SELECT		@progname = 'spMaintenanceLogFileSize'

--- Initialize Local Variables
SELECT	@pct_log_total = @log_pct_total / 100.0,
		@pct_log_used = @log_pct_used / 100.0,
		@vlf_optimal = 500, 
		@vlf_minimal = 250

--- Get Current Disk Information
INSERT INTO @diskspace (dbd_drive, dbd_capacityMB, dbd_usedMB, dbd_freeMB, dbd_free_pct) EXEC dbo.GetVolumeSpace ''

--- Get log file sizing for the requested database
INSERT INTO @logsize
	SELECT		database_id,
				file_id,
				name,
				physical_name,
				SUM(size * 8 / 1024),
				COUNT(*)
	FROM		master.sys.master_files mf
	WHERE		DB_NAME(database_id) NOT IN ('tempdb', 'model', 'ReportServerTempDB')
				AND DB_NAME(database_id) NOT IN (SELECT database_name FROM dbo.vwDatabase_DoNotMaintain)
				AND type = 1
	GROUP BY	database_id,
				file_id,
				name,
				physical_name

--- Retrieve the size of the data files and database log file info
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT		db.name
    FROM		master.sys.databases db
	WHERE		db.name NOT IN ('tempdb', 'ReportServerTempDB', 'model')
				AND db.name NOT IN (SELECT database_name FROM dbo.vwDatabase_DoNotMaintain)
	ORDER BY	db.database_id
OPEN crs
FETCH NEXT FROM crs INTO @database_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Retrieve the size of the data files
	SELECT	@sSQL = 'USE ' + QUOTENAME(@database_name) + '; DBCC SHOWFILESTATS WITH NO_INFOMSGS;'
	INSERT INTO @file_size (Fileid, FileGroup, TotalExtents, UsedExtents, name, physical_name) 
		EXEC (@sSQL)
	UPDATE @file_size SET database_id = DB_ID(@database_name) WHERE database_id IS NULL

	--- Retrieve the database log file info
	SELECT	@sSQL = 'DBCC LOGINFO(''' + @database_name + ''') WITH NO_INFOMSGS;'
	INSERT INTO @log_info (FileID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN) 
		EXEC (@sSQL)
	UPDATE @log_info SET database_id = DB_ID(@database_name) WHERE database_id IS NULL
	
	FETCH NEXT FROM crs INTO @database_name
  END
CLOSE crs
DEALLOCATE crs

--- Determine if the log file is overly fragmented and rebuild if necessary
;WITH data_size AS
(
	SELECT		database_id = database_id,
				total_MB = (CAST(LEFT(CAST(total_size_MB as varchar(1000)), 1) as int) + 1) * POWER(10, LEN(total_size_MB) - 1),
				used_MB = (CAST(LEFT(CAST(used_size_MB as varchar(1000)), 1) as int) + 1) * POWER(10, LEN(used_size_MB) - 1),
				suggested_logsize_MB = 
					(CASE	WHEN actual_tlog_MB > (total_size_MB * @pct_log_total) AND actual_tlog_MB > (used_size_MB * @pct_log_used) 
								THEN (CAST(LEFT(CAST(CAST(actual_tlog_MB as bigint) as varchar(1000)), 1) as int) + 1) 
									* POWER(10, LEN(CAST(actual_tlog_MB as bigint)) - 1)
							WHEN (total_size_MB * @pct_log_total) > (used_size_MB * @pct_log_used) 
								THEN (CAST(LEFT(CAST(CAST(total_size_MB * @pct_log_total as bigint) as varchar(1000)), 1) as int) + 1) 
										* POWER(10, LEN(CAST(total_size_MB * @pct_log_total as bigint)) - 1)
							ELSE (CAST(LEFT(CAST(CAST(used_size_MB * @pct_log_used as bigint) as varchar(1000)), 1) as int) + 1) 
									* POWER(10, LEN(CAST(used_size_MB * @pct_log_used as bigint)) - 1)
							END),
				current_vlf_count = (SELECT COUNT(*) FROM @log_info li WHERE database_id = t1.database_id)
	FROM		(
				SELECT		mf.database_id,
							SUM(CAST(ROUND((CAST(mf.size as decimal(18, 2)) * 8 / 1024), 0) as bigint)) total_size_MB,
							SUM(CAST(ROUND((CAST((t1.TotalExtents - t1.UsedExtents) * (mf.size / t1.TotalExtents) as decimal(18, 2)) * 8 / 1024), 0) as bigint)) free_size_MB,
							SUM(CAST(ROUND((CAST(t1.UsedExtents * (mf.size / t1.TotalExtents) as decimal(18, 2)) * 8 / 1024), 0) as bigint)) used_size_MB,
							ISNULL((SELECT MAX(backup_size/1024/1024) FROM msdb.dbo.backupset WHERE database_name = DB_NAME(mf.database_id) AND DATEDIFF(month, backup_start_date, GETDATE()) <= 2 AND type = 'I'), 0) actual_tlog_MB
				FROM		sys.master_files mf (NOLOCK)
				JOIN		@file_size t1 ON mf.name = t1.name
							AND mf.physical_name = t1.physical_name
				GROUP BY	mf.database_id
				) t1
),
calc_size AS
(
	SELECT		database_id,
				total_MB,
				used_MB,
				suggested_logsize_MB,
				(CASE	WHEN suggested_logsize_MB < 64 THEN (64/4)
						WHEN suggested_logsize_MB < 1024 THEN (1024/8)
						ELSE
							(CASE	WHEN ((suggested_logsize_MB / 8000 * 8000) + (CASE WHEN (suggested_logsize_MB % 8000) = 0 THEN 0 ELSE 8000 END)) = 
											((suggested_logsize_MB / 2000 * 2000) + (CASE WHEN (suggested_logsize_MB % 2000) = 0 THEN 0 ELSE 2000 END)) 
											THEN 500
									WHEN ((suggested_logsize_MB / 4000 * 4000) + (CASE WHEN (suggested_logsize_MB % 4000) = 0 THEN 0 ELSE 4000 END)) = 
											((suggested_logsize_MB / 2000 * 2000) + (CASE WHEN (suggested_logsize_MB % 2000) = 0 THEN 0 ELSE 2000 END)) 
											THEN 250
									ELSE 125 
									END)
						END) suggested_vlfsize_MB,
				(CASE	WHEN suggested_logsize_MB < 64 THEN 4
						WHEN suggested_logsize_MB < 1024 THEN 8
						ELSE 16 END) suggested_vlf_count,
				current_vlf_count
	FROM		data_size
),
action_ds AS
(
	SELECT		ls.database_id,
				ls.name,
				ls.physical_name,
				cs.total_MB,
				cs.used_MB,
				ls.logfile_size_MB,
				ls.logfile_count,
				cs.current_vlf_count,
				cs.suggested_logsize_MB,
				cs.suggested_vlfsize_MB,
				cs.suggested_vlf_count,
				(cs.suggested_vlfsize_MB * cs.suggested_vlf_count) log_incremental_size_MB,
				(CASE	WHEN cs.suggested_logsize_MB % (cs.suggested_vlfsize_MB * cs.suggested_vlf_count) = 0 
						THEN cs.suggested_logsize_MB / (cs.suggested_vlfsize_MB * cs.suggested_vlf_count)
						ELSE (cs.suggested_logsize_MB / (cs.suggested_vlfsize_MB * cs.suggested_vlf_count)) + 1
						END) log_incremental_count,
				(SELECT d.recovery_model_desc FROM master.sys.databases d WHERE name = @database_name) recovery_model
	FROM		calc_size cs
	JOIN		@logsize ls ON cs.database_id = ls.database_id
)
INSERT INTO @results (
		act_drive, database_id, name, physical_name, total_MB, used_MB, logfile_size_MB, 
		logfile_count, current_vlf_count, suggested_logsize_MB, suggested_vlfsize_MB, suggested_vlf_count,
		log_incremental_size_MB, log_incremental_count, recovery_model
	)
	SELECT		SUBSTRING(act.physical_name, 1, 2) act_drive,
				act.*
	FROM		action_ds act

--- Determine which action needs to be taken per database
;WITH drives AS
(
	SELECT		dd.dbd_drive,
				dd.dbd_capacityMB,
				dd.dbd_usedMB,
				dd.dbd_freeMB,
				dd.dbd_free_pct,
				SUM(res.logfile_size_MB) current_logfile_size_MB,
				SUM(res.suggested_logsize_MB) suggested_logfile_size_MB,
				((dd.dbd_freeMB - (SUM(res.suggested_logsize_MB) - SUM(res.logfile_size_MB))) / CAST(dd.dbd_capacityMB as float) * 100) new_free_PCT
	FROM		@results res
	JOIN		@diskspace dd ON res.act_drive = dd.dbd_drive
	WHERE		res.logfile_count = 1
				AND (
					res.logfile_size_MB <> res.suggested_logsize_MB
					OR
					ABS(res.current_vlf_count - (res.suggested_vlf_count * res.log_incremental_count)) <=5
				)
	GROUP BY	dd.dbd_drive,
				dd.dbd_capacityMB,
				dd.dbd_usedMB,
				dd.dbd_freeMB,
				dd.dbd_free_pct
	HAVING		((dd.dbd_freeMB - (SUM(res.suggested_logsize_MB) - SUM(res.logfile_size_MB))) / CAST(dd.dbd_capacityMB as float)) > (@min_disk_free_pct / 100.0)
)
UPDATE		res
SET			log_action = (CASE	
					WHEN logfile_count > 1 THEN 'Too Many Logfiles (> 1)'
					WHEN current_vlf_count > ((suggested_vlf_count * log_incremental_count) + 5) THEN 'Rebuild (Too Many VLF)'
					WHEN current_vlf_count < ((suggested_vlf_count * log_incremental_count) - 5) THEN 'Rebuild (Too Few VLF)'
					WHEN suggested_logsize_MB <= 1024 AND (suggested_logsize_MB > logfile_size_MB) THEN 'Grow'
					WHEN suggested_logsize_MB <= 1024 AND (suggested_logsize_MB < logfile_size_MB) THEN 'Shrink'
					WHEN suggested_logsize_MB > 1024 AND (log_incremental_size_MB * log_incremental_count) > logfile_size_MB THEN 'Grow'
					WHEN suggested_logsize_MB > 1024 AND (log_incremental_size_MB * log_incremental_count) < logfile_size_MB THEN 'Shrink'
					ELSE 'No Change' END)
FROM		@results res
WHERE		res.logfile_count = 1
			AND (
				res.suggested_logsize_MB <= res.logfile_size_MB
				OR
				res.act_drive IN (SELECT dbd_drive FROM drives)
			)

--- These variables only apply to the cursor below, so I wanted the definition
---		to be down here near the action
DECLARE	@recovery_model sysname, @database_id int, @name sysname, @suggested_logsize_MB bigint,
		@log_incremental_size_MB bigint, @log_incremental_count int, @iCount int

--- If in debug, display recordset
IF @debug_only = 1
  BEGIN
	SELECT		res.log_action,
				res.database_id,
				DB_NAME(res.database_id) database_name,
				res.recovery_model,
				res.name,
				res.logfile_size_MB,
				res.current_vlf_count,
				res.suggested_logsize_MB,
				(res.suggested_vlf_count * res.log_incremental_count) suggested_vlf_count,
				res.log_incremental_size_MB,
				res.log_incremental_count
	FROM		@results res
	WHERE		ISNULL(log_action, 'No Change') <> 'No Change'
	ORDER BY	log_action DESC, DB_NAME(database_id)
  END
  
--- Process the changes
DECLARE crs CURSOR FOR
	SELECT		recovery_model,
				database_id,
				name,
				suggested_logsize_MB,
				log_incremental_size_MB,
				log_incremental_count
	FROM		@results res
	WHERE		ISNULL(log_action, 'No Change') <> 'No Change'
	ORDER BY	log_action DESC, DB_NAME(database_id)
OPEN crs
FETCH NEXT FROM crs INTO @recovery_model, @database_id, @name, @suggested_logsize_MB, @log_incremental_size_MB, @log_incremental_count
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Different course of action based on the recovery_model
	IF @recovery_model = 'SIMPLE'
	  BEGIN
		--- For simple recovery, Checkpoint
		SELECT	@sSQL = 'USE ' + QUOTENAME(DB_NAME(@database_id))  + '; CHECKPOINT;'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)

			--- Log to the SQL Server error log
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
		  END
	  END
	  
	ELSE
	  BEGIN
		--- For non-simple recovery, backup log
		SELECT	@sSQL = 'EXEC spBackupLog @database_name = ''' + DB_NAME(@database_id) + ''';'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)
			
			--- Log to the SQL Server error log
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
		  END

		--- Shrink the log file to nothing (or as close as you can get).
		---		Doing an extra backup after this shrink and then another shrink
		---		seems to be necessary to get the active VLF back to the beginning
		---		of the transaction log in a non SIMPLE recovery model.
		SELECT	@sSQL = 'EXEC dbo.spShrinkFile @database_name = ''' + DB_NAME(@database_id) + ''', @minimum_size_MB = 0, @is_type_log_file = 1;'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)
				
			--- Log to the SQL Server error log
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
		  END
		  
		--- For non-simple recovery, backup log
		SELECT	@sSQL = 'EXEC spBackupLog @database_name = ''' + DB_NAME(@database_id) + ''';'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)
			
			--- Log to the SQL Server error log
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
		  END
	  END

	--- *******************************************************************
	--- The following applies regardless of recovery model
	--- *******************************************************************

	--- Shrink the log file to nothing (or as close as you can get)
	SELECT	@sSQL = 'EXEC dbo.spShrinkFile @database_name = ''' + DB_NAME(@database_id) + ''', @minimum_size_MB = 0, @is_type_log_file = 1;'
	IF @debug_only = 1
		PRINT @sSQL
	ELSE
	  BEGIN
		EXEC (@sSQL)
			
		--- Log to the SQL Server error log
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
	  END
	
	--- Grow the log file the specified number of times and increments
	IF @suggested_logsize_MB > 1024
	  BEGIN
		--- The new logsize is more than 1GB, so there is a loop to create uniform VLFs <= 500 MB
		SET @iCount = 1
		WHILE @iCount <= @log_incremental_count
		  BEGIN
			--- Grow the log file
			SELECT @sSQL = 'ALTER DATABASE ' + QUOTENAME(DB_NAME(@database_id)) + ' MODIFY FILE ( NAME = N''' + @name + ''', SIZE = ' + CAST((@log_incremental_size_MB * @iCount) as varchar(1000)) + 'MB )'
			IF @debug_only = 1
				PRINT @sSQL
			ELSE
			  BEGIN
				EXEC (@sSQL)
				
				--- Log to the SQL Server error log
				EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
			  END
			  
			--- Increment
			SET @iCount = @iCount + 1
		  END
	  END
	ELSE
	  BEGIN
		--- Get file current size
		DECLARE @size_now bigint
		SELECT	@size_now = (size * 8 / 1024)
		FROM	master.sys.master_files
		WHERE	database_id = @database_id
				AND name = @name
		
		--- If after the shrinks above, the current size is larger than the suggested size - skip
		IF @size_now < @suggested_logsize_MB
		  BEGIN
			--- Grow the log file
			SELECT @sSQL = 'ALTER DATABASE ' + QUOTENAME(DB_NAME(@database_id)) + ' MODIFY FILE ( NAME = N''' + @name + ''', SIZE = ' + CAST(@suggested_logsize_MB as varchar(1000)) + 'MB )'
			IF @debug_only = 1
				PRINT @sSQL
			ELSE
			  BEGIN
				EXEC (@sSQL)
				
				--- Log to the SQL Server error log
				EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
			  END
		  END
	  END

	--- Move to the next database
	FETCH NEXT FROM crs INTO @recovery_model, @database_id, @name, @suggested_logsize_MB, @log_incremental_size_MB, @log_incremental_count
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF